<?php defined('BASEPATH') OR die('No direct access allowed.');
/**
 * Description of Ajax_model
 *
 * Created on Dec 20, 2010
 * @author Ramon Gutierrez
 * @copyright Bitstop Network Services Inc.
 * @version 0.1
 */
class Ajax_model extends Model {
	public function Ajax_model() {
        parent::Model();
		$this->CI =& get_instance();
    }
	
	/*
	##  get_branches()
	##  ---------------------
	##  Query to populate Flexigrid table for Branches
	*/##################################################
	public function get_branches($client_id = false) {
		// Create the WHERE statement
		$where = "b.account_id = a.id AND b.client_id = c.id" . ($client_id ? " AND b.client_id = " . $client_id : "");

		// Create the SELECT statement
		$this->db->select("b.id AS branch_id, b.name, c.name AS company, CONCAT(a.first_name, ' ', a.last_name) AS branch_manager, b.account_id, b.product_key, b.client_id, b.activated", false);
		
		// Add search, order, and limit to the main query
		$this->CI->flexigrid->build_query();
		
		// Execute the query
		$this->db->where($where);
		$return['records'] = $this->db->get("clients c, branches b, accounts a");
		
		// Build the count query
		$this->db->select("COUNT(b.id) AS record_count")->from("clients c, branches b, accounts a");
		
		// Add search, order, and limit to the count query
		$this->CI->flexigrid->build_query(FALSE);
		$this->db->where($where);
		$record_count = $this->db->get();
		$row = $record_count->row();
		
		// Get record count
		$return['record_count'] = $row->record_count;
		
		// Return all
		return $return;
	}
	
	/*
	##  get_services()
	##  ---------------------
	##  Query to populate Flexigrid table for Services
	*/##################################################
	public function get_services($client_id = false) {
		// Determine query to be used
		if($client_id) {
			$query['main_query'] = "SELECT s.id AS id, s.title, s.prefix, s.access, cs.description FROM services s, clients_services cs WHERE cs.client_id = $client_id AND s.id = cs.service_id{SEARCH_STR}";
			$query['count_query'] = "SELECT COUNT(s.id) AS record_count FROM services s, clients_services cs WHERE cs.client_id = $client_id AND s.id = cs.service_id{SEARCH_STR}";
		} else {
			$query['main_query'] = "SELECT s.id AS id, s.title, s.prefix, s.access, cs.client_id, c.name FROM services s, clients c, clients_services cs WHERE access = 0 AND cs.client_id = c.id AND cs.service_id = s.id
									UNION SELECT s.id, s.title, s.prefix, s.access, NULL AS client_id, NULL AS name FROM services s WHERE access = 1{SEARCH_STR}";
			$query['count_query'] = "SELECT (SELECT COUNT(s.id) FROM services s WHERE access = 1{SEARCH_STR}) + (SELECT COUNT(s.id) FROM services s, clients c, clients_services cs WHERE access = 0 AND cs.client_id = c.id AND cs.service_id = s.id{SEARCH_STR}) AS record_count";
		}
		
		// Build and execute
		$build_querys = $this->CI->flexigrid->build_querys($query, FALSE);
		
		// Get contents
		$return['records'] = $this->db->query($build_querys['main_query']);
		
		// Get record count
		$get_record_count = $this->db->query($build_querys['count_query']);
		$row = $get_record_count->row();
		$return['record_count'] = $row->record_count; 
		
		// Return all
		return $return;
		
		/*
		// Create the WHERE statement
		$where = "b.account_id = a.id AND b.client_id = c.id" . ($client_id ? " AND b.client_id = " . $client_id : "");
		
		// Set row counter
		$this->db->query("SET @cnt := 0");
		
		// Create the SELECT statement
		$this->db->select("@cnt := @cnt + 1 AS x, b.id, b.name, c.name AS company, CONCAT(a.first_name, ' ', a.last_name) AS branch_manager, b.account_id, b.contact_number, b.client_id", false);
		
		// Add search, order, and limit to the main query
		$this->CI->flexigrid->build_query();
		
		// Execute the query
		$this->db->where($where);
		$return['records'] = $this->db->get("clients c, branches b, accounts a");
		
		// Build the count query
		$this->db->select("COUNT(b.id) AS record_count")->from("clients c, branches b, accounts a");
		
		// Add search, order, and limit to the count query
		$this->CI->flexigrid->build_query(FALSE);
		$this->db->where($where);
		$record_count = $this->db->get();
		$row = $record_count->row();
		
		// Get record count
		$return['record_count'] = $row->record_count;
		
		// Return all
		return $return;
		*/
	}
	
	/*
	##  get_accounts()
	##  ---------------------
	##  Query to populate Flexigrid table for Accounts
	*/##################################################
	public function get_accounts($client_id = false) {
		// Determine query to be used
		if($client_id) {
			$query['main_query'] = "SELECT a.id AS account_id, a.first_name AS first_name, a.last_name, a.username, a.account_type FROM accounts a, agents g WHERE g.client_id = $client_id AND a.id = g.account_id
									UNION SELECT a.id AS account_id, a.first_name, a.last_name, a.username, a.account_type FROM accounts a, branches b WHERE b.client_id = $client_id AND a.id = b.account_id{SEARCH_STR}";
			$query['count_query'] = "SELECT (SELECT COUNT(a.id) FROM accounts a, agents g WHERE g.client_id = $client_id AND a.id = g.account_id{SEARCH_STR}) + (SELECT COUNT(a.id) FROM accounts a, branches b WHERE b.client_id = $client_id AND a.id = b.account_id{SEARCH_STR}) AS record_count";
		} else {
			$query['main_query'] = "SELECT a.id AS account_id, a.first_name, a.last_name, a.username, a.account_type, c.name AS company, c.id AS client_id FROM accounts a, clients c WHERE a.id = c.account_id{SEARCH_STR}";
			$query['count_query'] = "SELECT COUNT(a.id) AS record_count FROM accounts a, clients c WHERE a.id = c.account_id{SEARCH_STR}";
		}
		
		// Build and execute
		$build_querys = $this->CI->flexigrid->build_querys($query, FALSE);
		
		// Get contents
		$return['records'] = $this->db->query($build_querys['main_query']);
		
		// Get record count
		$get_record_count = $this->db->query($build_querys['count_query']);
		$row = $get_record_count->row();
		$return['record_count'] = $row->record_count; 
		
		// Return all
		return $return;
	}
}

/* End of file ajax_model.php */
/* Location: ./system/application/models/ajax_model.php */